package com.zhf.dao;

import com.zhf.bean.Student;
import com.zhf.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @Author: ZhaoHuanfeng
 * @Date: 2020/10/17 15:48
 */
public class StudentDaoImp implements StudentDao {
    @Override
    public Student getStudentByID(String id) throws SQLException {
        QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());

        String sql="select * from t_student where student_id=?";

        Student student = queryRunner.query(sql, new BeanHandler<Student>(Student.class), id);
        return student;
    }

    @Override
    public List<Student> getStudents() throws SQLException {
        QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());

        String sql="select * from t_student";
        List<Student> students = queryRunner.query(sql, new BeanListHandler<Student>(Student.class));
        return students;
    }

    @Override
    public int addStudent(Student student) throws SQLException {
        Object name=student.getName();
        Object studentId=student.getStudent_id();
        Object sex=student.getSex();
        Object birthday=student.getBirthday();
        Object email=student.getEmail();
        Object remark = student.getRemark();
        Object stuclass = student.getStuclass();
        Object[] params={name,studentId,sex,birthday,email,remark,stuclass};

        QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());

        String sql="insert into t_student (NAME , student_id , sex , birthday , email , remark ,stuclass) values (?,?,?,?,?,?)";
        int update = queryRunner.update(sql, params);
        return update;
    }

    @Override
    public int updateStudent(Student student) throws SQLException {
        Object name=student.getName();
        Object studentId=student.getStudent_id();
        Object sex=student.getSex();
        Object birthday=student.getBirthday();
        Object email=student.getEmail();
        Object remark = student.getRemark();
        Object stuclass = student.getStuclass();
        Object[] params={name,sex,birthday,email,remark,stuclass,studentId};

        QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());

        String sql="update t_student set name=?, sex=?, birthday=?, email=?, remark=? ,stuclass=? where student_id=?";
        int update = queryRunner.update(sql, params);
        return update;
    }

    @Override
    public int deleteStudentByID(String id) throws SQLException {
        QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());

        String sql="delete from t_student where student_id=?";
        int update = queryRunner.update(sql, id);
        return update;
    }

    @Override
    public int isStudentInClass(String className) throws SQLException {
        String sql="select count(*) from t_student where stuclass=?";
        QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());
        Object query = queryRunner.query(sql, new ScalarHandler<>(), className);
        Number result=(Number) query;
        return result.intValue();
    }

}
